#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb
import time
import file_io


#import global_name
def get_time_stamp():
    ct = time.time()
    local_time = time.localtime(ct)
    data_head = time.strftime("%H:%M:%S", local_time)
    data_secs = (ct - long(ct)) * 1000
    time_stamp = "%s.%03d" % (data_head, data_secs)
    return time_stamp

class mysql(object):
    def __init__(self, **mysql_config):
        # self.ret = -1
        self.mysql_values = ""
        self.mysql_config = mysql_config
        self.error_logs = []

    def conn(self):
        try:
            self.connet = mdb.connect(**self.mysql_config)
            #return 0
        except mdb.Error, e:
            try:
                self.sqlError = "Error %d:%s" % (e.args[0], e.args[1])
            except IndexError:
                print "MySQL Error:%s" % str(e)
            print self.sqlError
            return -1

class mysql_exec(mysql):
    def __init__(self, _mysql):
        # self.ret = -1
        self.mysql_values = ""
        self.error_logs = []
        self._mysql = _mysql

    def show_dbs(self):
        self.databases = ""
        try:
            cursor = self._mysql.connet.cursor()
            sql = "SHOW DATABASES"
            # 执行SQL语句
            cursor.execute(sql)
            # 获取所有记录列表
            self.databases = cursor.fetchall()
            cursor.close()
            #print self.databases
        except  Exception:
            print "show_dbs", Exception.message
            return -1


    def show_c_db(self):
        self.c_dbs = ""
        try:
            cursor = self._mysql.connet.cursor()
            # sql语句--查询创建表语句
            sql_cdb = "SHOW CREATE DATABASE IF NOT EXISTS %s" % self._mysql.mysql_config['db']
            # 执行SQL语句
            cursor.execute(sql_cdb )
            self.c_dbs = cursor.fetchall()
            # print self.values
            cursor.close()
        except mdb.Error, e:
            try:
                self.sqlError = "Error %d:%s" % (e.args[0], e.args[1])
            except IndexError:
                print "MySQL Error:%s" % str(e)
            print self.sqlError

    def show_table(self):
        self.tables =""
        try:
            cursor = self._mysql.connet.cursor()
            sql_1 = "show tables"
            # 执行SQL语句
            cursor.execute(sql_1)
            # 获取所有记录列表
            self.tables = cursor.fetchall()
            cursor.close()
        except mdb.Error, e:
            try:
                self.sqlError = "Error %d:%s" % (e.args[0], e.args[1])
            except IndexError:
                print "MySQL Error:%s" % str(e)
            print self.sqlError
            return -1

    def show_c_table(self,table):
        self.c_tables = ""
        try:
            cursor = self._mysql.connet.cursor()
            # sql语句--查询创建表语句
            sql_c_tb = "SHOW CREATE TABLE %s" % table
            # 执行SQL语句
            cursor.execute(sql_c_tb)
            self.c_tables = cursor.fetchall()
            #print self.c_tables
            cursor.close()
        except mdb.Error, e:
            try:
                self.sqlError = "Error %d:%s" % (e.args[0], e.args[1])
            except IndexError:
                print "MySQL Error:%s" % str(e)
            print self.sqlError
            return -1

    def select_tb_value(self,table):
        self.values = ""
        self.fields = ""
        try:
            cursor = self._mysql.connet.cursor()
            sql_2 = "SELECT /*!40001 SQL_NO_CACHE */ * FROM %s" % table
            # 执行SQL语句
            cursor.execute(sql_2)
            # 获取所有记录值
            self.values = cursor.fetchall()
            # 获取结果字段名描述
            self.fields = cursor.description
            #print self.fields_des
            cursor.close()
        except mdb.Error, e:
            try:
                self.sqlError = "Error %d:%s" % (e.args[0], e.args[1])
            except IndexError:
                print "MySQL Error:%s" % str(e)
            print self.sqlError
            return -1

    def show_c_triggers(self):
        self.c_triggers = ""
        try:
            cursor = self._mysql.connet.cursor()
            # sql语句--查询创建表语句
            sql = "use `information_schema`"
            sql_c_tri = "SELECT /*!40001 SQL_NO_CACHE */ * FROM `TRIGGERS` WHERE TRIGGER_SCHEMA = '%s';" % self._mysql.mysql_config['db']
            # 执行SQL语句
            cursor.execute(sql)
            cursor.execute(sql_c_tri)
            self.c_triggers = cursor.fetchall()
            # print self.c_tables
            cursor.close()
        except mdb.Error, e:
            try:
                self.sqlError = "Error %d:%s" % (e.args[0], e.args[1])
            except IndexError:
                print "MySQL Error:%s" % str(e)
            print self.sqlError
            return -1

    # 查询表字段的其中一种方法
    # def __table_fields_des(self,table):
    #     self.fields = ""
    #     self.fields_des = ""
    #     try:
    #         cursor = self.connet.cursor()
    #         # sql语句--查询表字段信息
    #         sql_2 = "desc %s" % table
    #         # 执行SQL语句
    #         cursor.execute(sql_2)
    #         # 获取所有记录值
    #         self.fields = cursor.fetchall()
    #         # 获取结果的字段名描述
    #         self.fields_des = cursor.description
    #         # print self.values
    #         cursor.close()
    #         # print self.results
    #     except mdb.Error, e:
    #         try:
    #             self.sqlError = "Error %d:%s" % (e.args[0], e.args[1])
    #         except IndexError:
    #             print "MySQL Error:%s" % str(e)
    #         print self.sqlError
    #         return -1

    def sql_noresult(self,sqls):

        cursor = self._mysql.connet.cursor()
        try:
            #self.conn()
            if type(sqls) == tuple:
                for sql in sqls:
                    try:
                        cursor.execute(sql)
                    except mdb.Error, e:
                        try:
                            self.sqlError = "Error %d:%s" % (e.args[0], e.args[1])
                        except IndexError:
                            print "MySQL Error:%s" % str(e)
                        error = "%s Error:%s" % (sql,str(e))
                        self.error_logs.append(list(error))
            else:
                try:
                    cursor.execute(sqls)
                except mdb.Error, e:
                    try:
                        self.sqlError = "Error %d:%s" % (e.args[0], e.args[1])
                    except IndexError:
                        print "MySQL Error:%s" % str(e)
                    error = "%s Error:%s" % (sqls, str(e))
                    self.error_logs.append(list(error))
            #self.connet.close()
        except  Exception:
            print "__sql_noresult", Exception.message
            return -1
        finally:
            cursor.close()

class mysql_dump_exec(mysql_exec):
    def __init__(self, _mysql):
        """

        :rtype: object
        """
        mysql_exec.__init__(self, _mysql)
        sql_head_1 = "/*!40101 SET NAMES utf8 */;"
        sql_head_2 = "/*!40101 SET SQL_MODE=''*/;"
        sql_head_3 = "/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;"
        sql_head_4 = "/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;"
        sql_head_5 = "/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;"
        sql_head_6 = "/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;"
        self.sql_head = (sql_head_1, sql_head_2, sql_head_3, sql_head_4, sql_head_5, sql_head_6)

        sql_pre_set1 = "FLUSH /*!40101 LOCAL */ TABLES;"
        sql_pre_set2 = "FLUSH TABLES WITH READ LOCK;"
        sql_pre_set3 = "SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;"
        sql_pre_set4 = "START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */;"
        sql_pre_set5 = "UNLOCK TABLES;"
        self.sql_pre_set = (sql_pre_set1, sql_pre_set2, sql_pre_set3, sql_pre_set4, sql_pre_set5)

        self.sql_set_sp = "SAVEPOINT sp"
        self.sql_set_rtsp = "ROLLBACK TO SAVEPOINT sp"
        self.sql_set_resp = "RELEASE SAVEPOINT sp"
        self.sql_set_sqsc = "SET SQL_QUOTE_SHOW_CREATE=1"

        self.sql_use = "use %s" % _mysql.mysql_config['db']

        self.sql_version = "SELECT VERSION()"

        sql_end_1 = "/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;"
        sql_end_2 = "/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;"
        sql_end_3 = "/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;"
        sql_end_4 = "/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;"
        self.sql_end = (sql_end_1, sql_end_2, sql_end_3, sql_end_4)

    def sqldump_hd(self):
        try:
            #
            # 执行SQL语句
            self.sql_noresult(self.sql_head)
            self.sql_noresult(self.sql_pre_set)
            self.show_c_db()
            self.sql_noresult(self.sql_set_sp)
            self.show_table()
            self.show_c_triggers()
        except mdb.Error, e:
            try:
                self.sqlError = "Error %d:%s" % (e.args[0], e.args[1])
            except IndexError:
                print "MySQL Error:%s" % str(e)
            print self.sqlError
            return -1
        # finally:
        #     self.connet.close()
    def sqldump_md(self,table):
        try:
            self.sql_noresult(self.sql_use)
            self.sql_noresult(self.sql_set_sqsc)
            self.show_c_table(table)
            self.select_tb_value(table)
            self.sql_noresult(self.sql_set_rtsp)
        except mdb.Error, e:
            try:
                self.sqlError = "Error %d:%s" % (e.args[0], e.args[1])
            except IndexError:
                print "MySQL Error:%s" % str(e)
            print self.sqlError
            return -1
        # finally:
        #     self.connet.close()
    def sqldump_end(self):
        try:
            self.sql_noresult(self.sql_set_resp)
            # 未添加查询“存储过程”、“函数”、“视图”
            self.sql_noresult(self.sql_end)
        except mdb.Error, e:
            try:
                self.sqlError = "Error %d:%s" % (e.args[0], e.args[1])
            except IndexError:
                print "MySQL Error:%s" % str(e)
            print self.sqlError
            return -1
        self._mysql.connet.close()

class mysql_dump(object):
    def __init__(self, filename, _mysql):
        self.error = ""
        self.dump_sql = ""
        #self.databases = db
        self.filename = filename
        self._mysql = _mysql

    @property
    def mysql_package(self):
        print get_time_stamp() + ":start"
        # 拼接注释
        self.dump_sql = "/*\npthon_mysql_tools-v1.0\n: Database - %s\n" % self._mysql.mysql_config['db']
        self.dump_sql = self.dump_sql+ "***************************************************************" \
                                       "\n*/\n\n"

        # 创建数据库执行类实例
        _mysql = mysql_dump_exec(self._mysql)
        #_mysql.conn()
        _sqldump_hd = _mysql.sqldump_hd()
        # 拼接“开头设置”语句
        self.dump_sql = self.dump_sql + "\n".join(_mysql.sql_head)
        # 拼接“数据库”语句
        if _sqldump_hd == -1:
            self.error = self.error+ "mysqldump errror: mysql_statements exrc _mysql.sqldump_hd() is error\n"
            return self.error
        else:
            c_dbs = _mysql.c_dbs[0][1]
            db_sql = c_dbs + ";\n\n"
            db_sql = db_sql + "USE `%s`;\n\n" % self._mysql.mysql_config['db']
            self.dump_sql = self.dump_sql+ db_sql
            # 调用文件写入函数,调用一次清理一次"self.dump_sql"
            self.data_output()
            self.dump_sql = ""
        #print db_sql
        for t in _mysql.tables:
            # print r[0]
            table_name = t[0]

            _sqldump_md = _mysql.sqldump_md(table_name)
            if _sqldump_md== -1:
                self.error = self.error + "mysqldump errror: mysql_statements exrc _mysql.sqldump_md(%s) is error\n" % table_name
                return self.error
            else:
                # 拼接“表”语句
                table_sql = "/*Table structure for table `%s` */\n\n" % table_name
                table_sql = table_sql + "DROP TABLE IF EXISTS `%s`;\n\n" % table_name
                c_tables = _mysql.c_tables[0][1]
                table_sql = table_sql + c_tables + ";\n\n"
                self.dump_sql = self.dump_sql + table_sql
                #print table_sql
                # 拼接“数据插入”语句
                insert_sql = "/*Data for the table `%s` */\n\n" % table_name
                if _mysql.values == ():
                    pass
                    #print self.insert_sql
                else:
                    table_fields = []
                    table_vals = ""
                    table_valss = ""
                    for f in _mysql.fields:
                        table_field = "`"+f[0]+"`"
                        table_fields.append(table_field)
                    table_fields = ",".join(table_fields)
                    #使用列表生成试，join，map函数，6M数据减少24秒，减少时间%88.88。
                    t_list = ["(" + ",".join(map(self._get_str, res)) + ")" for res in _mysql.values]
                    table_valss = ",".join(t_list)
                    # for r in _mysql.values:
                    #     #print r
                    #     for v in r:
                    #         #print type(v)
                    #         if type(v) == long:
                    #             table_val = str(v)
                    #         elif v == None:
                    #             table_val = "NULL"
                    #         else:
                    #             v = str(v)
                    #             table_val = "'"+ v+ "'"
                    #
                    #         if table_vals == "":
                    #             table_vals = table_val
                    #         else:
                    #             table_vals = table_vals+ ","+ table_val
                    #     if table_valss == "":
                    #         table_valss = "(" + table_vals + ")"
                    #     else:
                    #         table_valss = table_valss+ ","+ "("+ table_vals+ ")"
                    #     table_vals = ""
                    #print table_valss
                    if table_valss == "":
                        pass
                    else:
                        insert_sql = insert_sql+ "insert  into `%s`" % table_name+ \
                                     "(" + table_fields + ")"+ " values "+ table_valss +";\n\n"
                self.dump_sql = self.dump_sql + insert_sql
                print get_time_stamp() + ":" + table_name
                # 调用文件写入函数,调用一次清理一次"self.dump_sql"
                self.data_output()
                self.dump_sql = ""
        #拼接“触发器”语句
        sql_triggers = ""
        if _mysql.c_triggers == ():
            pass
        else:
            for c_triggers in _mysql.c_triggers:
                sql_triggers = sql_triggers + \
                               "/* Trigger structure for table `%s` */\n\nDELIMITER $$\n\n"\
                               % c_triggers[6]
                sql_triggers = sql_triggers + \
                               "/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `%s` */$$\n\n"\
                               % c_triggers[2]
                sql_triggers = sql_triggers + \
                               "/*!50003 CREATE */ /*!50017 DEFINER = '%s' */ /*!50003 TRIGGER \`%s\` %s %s ON \`%s\` FOR EACH %s %s */$$\n\n\nDELIMITER ;\n\n"\
                               % (c_triggers[-4], c_triggers[2], c_triggers[11], c_triggers[3], c_triggers[6], c_triggers[10], c_triggers[9])
                self.dump_sql = self.dump_sql+ sql_triggers
        #print self.dump_sql
        _sqldump_end = _mysql.sqldump_end()
        if _sqldump_end == -1:
            self.error = self.error+ "mysqldump errror: mysql_statements exrc _mysql.sqldump_end() is error\n"
            return self.error
        else:
            self.dump_sql = self.dump_sql + "\n".join(_mysql.sql_end)
        # 调用文件写入函数,调用一次清理一次"self.dump_sql"
        typ, msg = self.data_output()
        self.dump_sql = ""
        #del _mysql
        print get_time_stamp() + ":end"
        return typ, msg

    def _get_str(self,v):
        # _get_str函数，由于mysql返回的值有long，None等类型，需要根据实际转换成str
        if type(v) == long:
            v = str(v)
        elif v == None:
            v = "NULL"
        else:
            v = str(v)
            v = "'" + v + "'"
        return v

    # 学习过程
    # def __old_mysql_statements(self):
    #
    #     g_sql_db_show = "SHOW CREATE DATABASE IF NOT EXISTS %s" % self.databases
    #     mysql_2 = mysql_exec(**mysql_config)
    #     db_structure = mysql_2.structure(g_sql_db_show)
    #     if db_structure == -1:
    #         pass
    #     else:
    #         db_stru = mysql_2.values[0][1]
    #         self.db_sql = db_stru + ";\n\n"
    #         self.db_sql = self.db_sql + "USE `%s`;\n\n" % self.databases
    #     print self.db_sql
    #     del mysql_2
    #
    #     mysql_3 = mysql_exec(**mysql_config)
    #     show_table = mysql_3.show_tables()
    #     if show_table == -1:
    #         pass
    #     else:
    #         for t in mysql_3.tables:
    #             # print r[0]
    #             table_name = t[0]
    #             g_sql_tb_show = "SHOW CREATE TABLE %s" % table_name
    #             self.table_sql = "/*Table structure for table `%s` */\n\n" % table_name
    #             self.table_sql = self.table_sql + "DROP TABLE IF EXISTS `%s`;\n\n" % table_name
    #
    #             mysql_4 = mysql_exec(table_name, **mysql_config)
    #             table_structure = mysql_4.structure(g_sql_tb_show)
    #             if table_structure == -1:
    #                 pass
    #             else:
    #                 # 因为cursor.fetchall() 函数结果为元组类型格式是：（sql语句结果，）
    #                 # 又因为sql语句结果包含了多个值,所以其类型也为元组
    #                 # 所以mysql_4.values[0][1]表示从结果中取出第0组数据，再从第0组数据中取出第1组数据.
    #                 table_stru = mysql_4.values[0][1]
    #                 self.table_sql = self.table_sql + table_stru + "\n"
    #             del mysql_4
    #
    #             mysql_5 = mysql_exec(table_name, **mysql_config)
    #             table_values = mysql_5.value_get()
    #             mysql_5
    #             if table_values == -1:
    #                 pass
    #             else:
    #                 table_fields = []
    #                 table_vals = ""
    #                 table_valss = ""
    #                 for f in mysql_5.fields_des:
    #                     table_field = f[0]
    #                     table_fields.append(table_field)
    #                 table_fields = tuple(table_fields)
    #                 table_fields = str(table_fields)
    #                 #print table_fields
    #                 for r in mysql_5.values:
    #                     if r == "":
    #                         print "111"
    #                         break
    #                     for v in r:
    #                         v = unicode(v)
    #                         table_val = "'"+ v+ "'"
    #                         if table_val == "'None'":
    #                             table_val = "NULL"
    #                         if table_vals == "":
    #                             table_vals = table_val
    #                         else:
    #                             table_vals = table_vals+ ", "+ table_val
    #                     if table_valss == "":
    #                         table_valss = "(" + table_vals + ")"
    #                     else:
    #                         table_valss = table_valss+ ","+ "("+ table_vals+ ")"
    #                 #print table_valss
    #             if table_valss == "":
    #                 self.insert_sql = "/*Data for the table `%s` */\n\n" % table_name
    #             else:
    #                 self.insert_sql = "/*Data for the table `%s` */\n\n" % table_name
    #                 self.insert_sql = self.insert_sql+ "insert  into `%s`" % table_name+ table_fields+ " values "+ table_valss+";\n\n"
    #             print self.table_sql
    #             print self.insert_sql

    def data_output(self):
        f = file_io.file()
        typ,msg = f.write(self.dump_sql,self.filename)
        return typ, msg
# def test():
#     g_sql_tb_show = "SHOW CREATE TABLE %s" % "t_authen"
#
#     mysql = mysql_exec("t_authen", **mysql_config)
#     table_structure = mysql.structure(g_sql_tb_show)
#     if table_structure == -1:
#         pass
#     else:
#         # 因为cursor.fetchall() 函数结果为元组类型格式是：（sql语句结果，）
#         # 又因为sql语句结果包含了多个值,所以其类型也为元组
#         # 所以mysql_4.values[0][1]表示从结果中取出第0组数据，再从第0组数据中取出第1组数据.
#         table_stru = mysql.values[0][1]
#
#         print table_stru
#     del mysql


# if __name__ == "__main__":
#     mysql_config = {
#         'host': "127.0.0.1",
#         'port': 3306,
#         'user': "root",
#         'passwd': "123456",
#         'db': "psm70_zhengzhou",
#         'charset': "utf8"
#     }
#
#     dump = mysql_dump(**mysql_config)
#     dump.mysql_package


